﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections;
using Devart.Data.SQLite;
using ExpreRec.DataAccess.Util;
using System.Data;

namespace ExpreRec.DataAccess
{
    public class VariableDAL
    {
        private static IDictionary<string, string> consts = new Dictionary<string, string>();

        private const string SQL_SELECT_VARIABLE_LIST = "SELECT KEY, VALUE FROM Variable;";
        private const string SQL_UPDATE_VARIABLE = "UPDATE Variable SET VALUE = @VALUE WHERE KEY = @KEY;";
        private const string SQL_INSERT_VARIABLE = "INSERT INTO Variable(KEY, VALUE) VALUES(@KEY, @VALUE);";



        public void Refresh()
        {
            consts.Clear();

            using (SQLiteDataReader reader = SQLiteHelper.ExecuteReader(SQLiteHelper.SQLiteConnectionString, CommandType.Text, SQL_SELECT_VARIABLE_LIST, null))
            {
                SafeSQLiteDataReader rdr = new SafeSQLiteDataReader(reader);
                while (rdr.Read())
                {
                    string key = rdr.GetString(0).ToUpper();
                    string value = rdr.GetString(1);

                    consts.Add(new KeyValuePair<string, string>(key, value));
                }
            }
        }

        public string GetValue(string key)
        {
            if (consts.Count == 0)
                Refresh();

            if (!string.IsNullOrWhiteSpace(key))
            {
                key = key.ToUpper();
                if (consts.ContainsKey(key))
                    return consts[key];
            }

            return null;
        }

        public IList<KeyValuePair<string, string>> GetList()
        {
            if (consts.Count == 0)
                Refresh();

            return consts.ToArray<KeyValuePair<string, string>>();
        }

        public void UpdateValue(string key, string value)
        {
            key = key.ToUpper();

            string original = GetValue(key);

            SQLiteParameter[] parms = {
                new SQLiteParameter("@KEY", SqlDbType.NVarChar),
                new SQLiteParameter("@VALUE", SqlDbType.NVarChar)
            };

            parms[0].Value = SQLParamConverter.ObjectToSqlObject(key);
            parms[1].Value = SQLParamConverter.ObjectToSqlObject(value);

            if (original == null)
            {
                //insert
                SQLiteHelper.ExecuteNonQuery(SQLiteHelper.SQLiteConnectionString, CommandType.Text, SQL_INSERT_VARIABLE, parms);
            }
            else
            {
                //update
                SQLiteHelper.ExecuteNonQuery(SQLiteHelper.SQLiteConnectionString, CommandType.Text, SQL_UPDATE_VARIABLE, parms);
            }

            Refresh();
        }
    }
}
